package Dao.Admin.Imp;

import Dao.Admin.GradingManagementDao;
import Entity.Grade.Grading;
import Entity.Role.Role;
import Entity.Role.Student;
import Entity.Role.Teacher;
import Util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class GradingManagementDaoImp implements GradingManagementDao {
    private PreparedStatement ps = null;
    private ResultSet rs = null;

    /**
     * 查询评阅教师角色是否存在
     */
    public Integer reviewerQuery(String tno, Connection conn) {
        int res = 0;
        try {
            String sql = " select COUNT(tno) from role where tno = ? and name = '评阅教师' ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, tno);
            rs = ps.executeQuery();
            if (rs.next()) {
                res = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return res;
    }

    /**
     * 查询答辩组教师角色是否存在
     */
    public Integer replyReviewerQuery(String tno, Connection conn) {
        int res = 0;
        try {
            String sql = " select COUNT(tno) from role where tno = ? and name = '答辩组教师' ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, tno);
            rs = ps.executeQuery();
            if (rs.next()) {
                res = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return res;
    }

    /**
     * 查询学生是否已分配评阅教师
     */
    public Integer stuReviewerQuery(String sno, Connection conn) {
        int res = 0;
        try {
            String sql = " select COUNT(sno) from grading where sno = ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, sno);
            rs = ps.executeQuery();
            if (rs.next()) {
                res = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return res;
    }

    /**
     * 新增评阅教师角色
     */
    public Integer reviewerAssign(String tno, Connection conn) {
        int result = 0;
        try {
            String sql = " insert into role( name , tno) " +
                    " values ('评阅教师',?) ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, tno);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return result;
    }

    /**
     * 新增答辩组教师角色
     */
    public Integer replyReviewerAssign(String tno, Connection conn) {
        int result = 0;
        try {
            String sql = " insert into role( name , tno) " +
                    " values ('答辩组教师',?) ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, tno);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return result;
    }


    /**
     * 新增评阅教师表师生对信息
     */
    public Integer gradingAdd(String tno, String sno, Connection conn) {
        int result = 0;
        try {
            String sql = " insert into grading( tno , sno ) " +
                    " values ( ? , ? ) ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, tno);
            ps.setString(2, sno);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return result;
    }

    /**
     * 按类型查询教师角色
     */
    public List<Role> reviewerAllQuery(Integer type, Connection conn) {
        List<Role> roles = new ArrayList<>();
        try {
            String sql = " select id,name,tno from role where name = ?";
            ps = conn.prepareStatement(sql);
            if (type == 1) {
                ps.setString(1, "评阅教师");
            } else if (type == 2) {
                ps.setString(1, "答辩组教师");
                return roles;
            } else {
                return roles;
            }
            rs = ps.executeQuery();
            while (rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("name");
                String tNO = rs.getString("tno");
                Role role = new Role(id, name, tNO);
                roles.add(role);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return roles;
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return roles;
    }

    @Override
    public List<Teacher> getAllByRole(String roleName, Connection conn) {
        List<Teacher> teachers = new ArrayList<>();
        String sql = "select t.tno,t.name,t.sex,t.post,t.tel,t.email,t.collegeid,t.department " +
                "from teacher t,role r " +
                "where t.tno = r.tno and r.name = ?";
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, roleName);
            rs = ps.executeQuery();
            while (rs.next()) {
                Teacher teacher = new Teacher();
                teacher.setTNO(rs.getString(1));
                teacher.setName(rs.getString(2));
                teacher.setSex(rs.getString(3));
                teacher.setPost(rs.getString(4));
                teacher.setTel(rs.getString(5));
                teacher.setEmail(rs.getString(6));
                teacher.setCollegeId(rs.getString(7));
                teacher.setDepartment(rs.getString(8));
                teachers.add(teacher);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeMySQLResource(ps, rs);
        }
        return teachers;
    }


}
